const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
require('dotenv').config();

async function initScheduleTables() {
  let connection;
  
  try {
    // 从环境变量或默认配置创建数据库连接
    const config = {
      host: process.env.DB_HOST || 'localhost',
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      database: process.env.DB_NAME || 'linkmate',
      multipleStatements: true
    };

    console.log('连接数据库:', config.host, config.database);
    connection = await mysql.createConnection(config);

    console.log('开始创建课表相关表...');
    
    // 分别创建每个表
    const createTables = [
      // 1. 课表主表
      `CREATE TABLE IF NOT EXISTS schedules (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL COMMENT '用户ID',
        title VARCHAR(200) NOT NULL COMMENT '课表标题',
        semester VARCHAR(50) NOT NULL COMMENT '学期',
        start_week INT NOT NULL DEFAULT 1 COMMENT '开始周',
        total_weeks INT NOT NULL DEFAULT 20 COMMENT '总周数',
        start_date DATE DEFAULT NULL COMMENT '学期开始日期',
        end_date DATE DEFAULT NULL COMMENT '学期结束日期',
        class_times JSON DEFAULT NULL COMMENT '上课时间配置',
        duration INT DEFAULT 45 COMMENT '每节课持续时间(分钟)',
        description TEXT DEFAULT NULL COMMENT '课表描述',
        is_active BOOLEAN DEFAULT TRUE COMMENT '是否激活',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_semester (semester),
        INDEX idx_is_active (is_active),
        INDEX idx_created_at (created_at)
      ) COMMENT '课表主表'`,
      
      // 2. 课程表
      `CREATE TABLE IF NOT EXISTS courses (
        id INT PRIMARY KEY AUTO_INCREMENT,
        schedule_id INT NOT NULL COMMENT '课表ID',
        name VARCHAR(200) NOT NULL COMMENT '课程名称',
        teacher VARCHAR(100) DEFAULT NULL COMMENT '任课教师',
        classroom VARCHAR(100) DEFAULT NULL COMMENT '教室',
        day_of_week TINYINT NOT NULL COMMENT '星期几(1-7)',
        time_slot TINYINT NOT NULL COMMENT '第几节课(1-12)',
        duration TINYINT DEFAULT 2 COMMENT '持续节数',
        credits DECIMAL(3,1) DEFAULT 3.0 COMMENT '学分',
        course_type ENUM('required', 'elective', 'practice') DEFAULT 'required' COMMENT '课程类型',
        description TEXT DEFAULT NULL COMMENT '课程描述',
        cell_position VARCHAR(10) DEFAULT NULL COMMENT '原始单元格位置',
        is_active BOOLEAN DEFAULT TRUE COMMENT '是否激活',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
        INDEX idx_schedule_id (schedule_id),
        INDEX idx_day_of_week (day_of_week),
        INDEX idx_time_slot (time_slot),
        INDEX idx_course_type (course_type),
        INDEX idx_is_active (is_active)
      ) COMMENT '课程表'`,
      
      // 3. 课程周次表
      `CREATE TABLE IF NOT EXISTS course_weeks (
        id INT PRIMARY KEY AUTO_INCREMENT,
        course_id INT NOT NULL COMMENT '课程ID',
        week_number INT NOT NULL COMMENT '周次',
        is_active BOOLEAN DEFAULT TRUE COMMENT '是否激活',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
        UNIQUE KEY unique_course_week (course_id, week_number),
        INDEX idx_course_id (course_id),
        INDEX idx_week_number (week_number)
      ) COMMENT '课程周次表'`,
      
      // 4. 课表分析记录表
      `CREATE TABLE IF NOT EXISTS schedule_analyses (
        id INT PRIMARY KEY AUTO_INCREMENT,
        schedule_id INT NOT NULL COMMENT '课表ID',
        file_name VARCHAR(255) NOT NULL COMMENT '原始文件名',
        file_path VARCHAR(500) NOT NULL COMMENT '文件路径',
        file_size BIGINT DEFAULT 0 COMMENT '文件大小(字节)',
        analysis_type ENUM('ai', 'manual') DEFAULT 'ai' COMMENT '分析类型',
        confidence DECIMAL(3,2) DEFAULT 0.00 COMMENT '置信度(0-1)',
        table_structure JSON DEFAULT NULL COMMENT '表格结构分析结果',
        semester_info JSON DEFAULT NULL COMMENT '学期信息分析结果',
        suggestions JSON DEFAULT NULL COMMENT '建议信息',
        warnings JSON DEFAULT NULL COMMENT '警告信息',
        questions JSON DEFAULT NULL COMMENT '问题信息',
        analysis_log TEXT DEFAULT NULL COMMENT '分析日志',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
        INDEX idx_schedule_id (schedule_id),
        INDEX idx_analysis_type (analysis_type),
        INDEX idx_confidence (confidence),
        INDEX idx_created_at (created_at)
      ) COMMENT '课表分析记录表'`,
      
      // 5. 课表分享表
      `CREATE TABLE IF NOT EXISTS schedule_shares (
        id INT PRIMARY KEY AUTO_INCREMENT,
        schedule_id INT NOT NULL COMMENT '课表ID',
        share_token VARCHAR(100) UNIQUE NOT NULL COMMENT '分享令牌',
        share_type ENUM('public', 'private', 'password') DEFAULT 'private' COMMENT '分享类型',
        password VARCHAR(100) DEFAULT NULL COMMENT '访问密码',
        expires_at TIMESTAMP DEFAULT NULL COMMENT '过期时间',
        access_count INT DEFAULT 0 COMMENT '访问次数',
        is_active BOOLEAN DEFAULT TRUE COMMENT '是否激活',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON DELETE CASCADE,
        INDEX idx_schedule_id (schedule_id),
        INDEX idx_share_token (share_token),
        INDEX idx_share_type (share_type),
        INDEX idx_expires_at (expires_at),
        INDEX idx_is_active (is_active)
      ) COMMENT '课表分享表'`,
      
      // 6. 课表导入历史表
      `CREATE TABLE IF NOT EXISTS schedule_imports (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL COMMENT '用户ID',
        file_name VARCHAR(255) NOT NULL COMMENT '文件名',
        file_type VARCHAR(50) NOT NULL COMMENT '文件类型',
        file_size BIGINT DEFAULT 0 COMMENT '文件大小(字节)',
        import_status ENUM('pending', 'processing', 'success', 'failed') DEFAULT 'pending' COMMENT '导入状态',
        courses_count INT DEFAULT 0 COMMENT '课程数量',
        error_message TEXT DEFAULT NULL COMMENT '错误信息',
        import_log TEXT DEFAULT NULL COMMENT '导入日志',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_import_status (import_status),
        INDEX idx_created_at (created_at)
      ) COMMENT '课表导入历史表'`
    ];
    
    // 逐个执行CREATE TABLE语句
    for (let i = 0; i < createTables.length; i++) {
      try {
        console.log(`创建表 ${i + 1}/${createTables.length}...`);
        await connection.execute(createTables[i]);
        console.log(`✅ 表 ${i + 1} 创建成功`);
      } catch (error) {
        console.log(`❌ 表 ${i + 1} 创建失败:`, error.message);
        // 继续创建其他表
      }
    }
    
    console.log('✅ 课表相关表创建成功！');
    
    // 验证表是否创建成功
    const [tables] = await connection.execute("SHOW TABLES LIKE 'schedules'");
    if (tables.length > 0) {
      console.log('✅ schedules表创建成功');
    } else {
      console.log('❌ schedules表创建失败');
    }
    
    const [coursesTable] = await connection.execute("SHOW TABLES LIKE 'courses'");
    if (coursesTable.length > 0) {
      console.log('✅ courses表创建成功');
    } else {
      console.log('❌ courses表创建失败');
    }
    
  } catch (error) {
    console.error('❌ 创建表失败:', error.message);
    console.error('详细错误:', error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

// 如果直接运行此脚本
if (require.main === module) {
  initScheduleTables();
}

module.exports = initScheduleTables;
